﻿using System;
using System.Collections.Generic;
using System.Data;
using DSPL.DataAccess;
using DSPL.Contracts;
using DSPL.Contracts.Interfaces;
using DSPL.Contracts.Common;


namespace DSPL.Services.Services
{
    public class MasterReadService : IMasterReadService
    {

        public DataTable GetLocationType()
        {
            var dtLocationType = new DataTable(); ;
            var readService = DatabaseAccess.Instance;
            // string sqlQuery = "Select LTRIM(RTRIM(LoctCode)) as LoctCode,LoctName From fLocType";
            // string sqlQuery = "Select  LocTypePk,LoctCode,LoctName From fLocType";
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "LocTypePk" });
            columnList.Add(new ColumnWithValue { ColumnName = "LoctCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "LoctName" });

            var locationQuery = new Query { TableName = "fLocType", QType = QueryType.SELECT, ColValues = columnList.ToArray() };
            dtLocationType = readService.ExcecuteSelect(locationQuery);
            return dtLocationType;
        }


        public DataTable GetSupplierTaxData(int supplierPk)
        {
            var readMasterData = DatabaseAccess.Instance;


            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "fCreTax.TaxRegNo" });
            columnList.Add(new ColumnWithValue { ColumnName = "fCreTax.TaxPk" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fCreTax", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            whereList.Add(new ColumnWithValue { ColumnName = "fCreTax.SupplierPk", Comparison = QComparison.EQUALS, ColumnValue = supplierPk });
            var supplierTaxQuery = new Query { TableName = "fCreTax", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return readMasterData.ExcecuteSelect(supplierTaxQuery);
        }

        public DataTable GetCustomerTaxData(int customerPk)
        {
            var readMasterData = DatabaseAccess.Instance;


            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "fDebTax.TaxRegNo" });
            columnList.Add(new ColumnWithValue { ColumnName = "fDebTax.TaxPk" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fDebTax", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            whereList.Add(new ColumnWithValue { ColumnName = "fDebTax.DebPk", Comparison = QComparison.EQUALS, ColumnValue = customerPk });
            var supplierTaxQuery = new Query { TableName = "fDebTax", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return readMasterData.ExcecuteSelect(supplierTaxQuery);
        }


        public DataTable GetMasterData(string query)
        {
            var readMasterData = DatabaseAccess.Instance;
            var masterQuery = new Query { QueryString = query, QType = QueryType.SELECTOTHER };

            return (readMasterData.ExcecuteSelect(masterQuery));

        }

        public DataTable GetConfigData()
        {
            var readMasterData = DatabaseAccess.Instance;
            var masterQuery = new Query { TableName = "fControl", QType = QueryType.SELECTALL };

            return (readMasterData.ExcecuteSelect(masterQuery));

        }

        public DataTable GetReasonTypes()
        {
            var dtReasonType = new DataTable(); ;
            var readService = DatabaseAccess.Instance;
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "ReaTypePk" });
            columnList.Add(new ColumnWithValue { ColumnName = "ReaTcode" });
            columnList.Add(new ColumnWithValue { ColumnName = "ReaTname" });

            var reasonQuery = new Query { TableName = "FReaTyp", QType = QueryType.SELECT, ColValues = columnList.ToArray() };

            dtReasonType = readService.ExcecuteSelect(reasonQuery);
            return dtReasonType;
        }

        public bool IsVendorCodeExist(string vendorCode, int itemIntKey)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue { ColumnName = "VenPCode", Comparison = QComparison.EQUALS, ColumnValue = vendorCode, ColumnConnector = QLogicOperator.AND });
            whereList.Add(new ColumnWithValue { ColumnName = "ItemPk", Comparison = QComparison.NOTEQUAL, ColumnValue = itemIntKey });
            columnList.Add(new ColumnWithValue { ColumnName = "VenPCode" });
            var vendorQuery = new Query { TableName = "fItems", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(vendorQuery).Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        public DataTable GetItemPriceLevels(int itemIntKey)
        {
            var readMasterData = DatabaseAccess.Instance;
            var dtItemPrice = new DataTable();
            var dtItemPriceLevel = new DataTable();
            var parameterList = new List<Parameter>();
            string sqlItemPrice = "select a.ItemPk,a.PriLvlPk,b.PrilCode,b.PrilName,a.Price,a.AddUser,a.AddDate,a.AddMach,a.TxnUser,a.TxnDate,a.TxnMach from fItemPri a inner join fPriLvl b On a.PriLvlPk=b.PriLvlPk where a.ItemPk=@itemCode";
            parameterList.Add(new Parameter { ParamName = "@itemCode", ParamValue = itemIntKey });
            var itemPriceQuery = new Query { QueryString = sqlItemPrice, QType = QueryType.SELECTOTHER, ParameterList = parameterList.ToArray() };
            dtItemPrice = readMasterData.ExcecuteSelect(itemPriceQuery, true);
            string sqlPriceLvel = "Select * from fPrilvl Where PriLvlPk Not In(Select PriLvlPk From fItemPri Where ItemPk=@itemCode)";
            var priceQuery = new Query { QueryString = sqlPriceLvel, QType = QueryType.SELECTOTHER, ParameterList = parameterList.ToArray() };
            dtItemPriceLevel = readMasterData.ExcecuteSelect(priceQuery, true);
            if (dtItemPriceLevel.Rows.Count > 0)
            {
                dtItemPriceLevel.Columns.Add("ItemPk", typeof(System.Int32));
                dtItemPriceLevel.Columns.Add("Price", typeof(System.Double));
                dtItemPriceLevel.Columns.Add("TxnUser", typeof(System.String));
                dtItemPriceLevel.Columns.Add("TxnDate", typeof(System.DateTime));
                dtItemPriceLevel.Columns.Add("TxnMach", typeof(System.String));

                foreach (DataRow drPriceLvl in dtItemPriceLevel.Rows)
                {
                    drPriceLvl["ItemPk"] = itemIntKey;
                    drPriceLvl["Price"] = 0.0;
                    drPriceLvl["TxnUser"] = GlobalVariables.LoggedInUser;
                    drPriceLvl["TxnDate"] = DateTime.Now;
                    drPriceLvl["AddDate"] = DateTime.Now;
                    drPriceLvl["TxnMach"] = Environment.MachineName + "#" + Environment.UserName;
                    dtItemPrice.ImportRow(drPriceLvl);
                }
            }
            return dtItemPrice;
        }

        public DataTable GetPriceLevelStatus()
        {
            var readMasterData = DatabaseAccess.Instance;
            var dtPriceLevelStatus = new DataTable();
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "Status" });
            whereList.Add(new ColumnWithValue { ColumnName = "ConCode", Comparison = QComparison.EQUALS, ColumnValue = "TXN0015" });
            var priceQuery = new Query { TableName = "fSTConFig", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            dtPriceLevelStatus = readMasterData.ExcecuteSelect(priceQuery);
            return dtPriceLevelStatus;

        }

        public DataTable GetDebtorPriceLevel(int debtorPk)
        {
            var dtDebtorPriceLevel = new DataTable();
            var readService = DatabaseAccess.Instance;
            string sqlQuery = "Select c.CurCode,p.PrilCode,b.DebCode,b.DebPk,a.PriLvlPk,a.CurPk from fDebPriLvl a inner join fDebtor b on a.DebPk =b.DebPk inner join fCurrency c on a.curPk=c.CurPk  " +
                                " inner join fPriLvl p on a.PriLvlPk =p.PriLvlPk where b.DebPk=@debtorPk";
            var parametersPriceLevel = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@debtorPk",ParamValue=debtorPk},
                                 };

            var priceLevelQuery = new Query() { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = parametersPriceLevel.ToArray() };

            dtDebtorPriceLevel = readService.ExcecuteSelect(priceLevelQuery, true);
            return dtDebtorPriceLevel;
        }

        public bool IsSalesRepActive(int salesRepPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "RepCode" });
            whereList.Add(new ColumnWithValue { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "RepPk", Comparison = QComparison.EQUALS, ColumnValue = salesRepPk });
            var salRepQuery = new Query { TableName = "fSalRep", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(salRepQuery).Rows.Count > 0)
            {
                return true;
            }

            return false;
        }

        public bool IsPriceLevelActive(int priceLevelPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "PrilCode" });
            whereList.Add(new ColumnWithValue { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "PriLvlPk", Comparison = QComparison.EQUALS, ColumnValue = priceLevelPk });
            var priLvlQuery = new Query { TableName = "fPriLvl", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(priLvlQuery).Rows.Count > 0)
            {
                return true;
            }

            return false;
        }

        public bool IsSupplierActive(int supplierPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "SupCode" });
            whereList.Add(new ColumnWithValue { ColumnName = "Status", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "SupplierPk", Comparison = QComparison.EQUALS, ColumnValue = supplierPk });
            var supplierQuery = new Query { TableName = "fSupplier", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(supplierQuery).Rows.Count > 0)
            {
                return true;
            }

            return false;
        }

        public bool IsTaxCombinationActive(int taxComPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "TaxComCode" });
            whereList.Add(new ColumnWithValue { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "TaxComPk", Comparison = QComparison.EQUALS, ColumnValue = taxComPk });
            var taxComQuery = new Query { TableName = "fTaxHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(taxComQuery).Rows.Count > 0)
            {
                return true;
            }

            return false;
        }

        public bool IsItemActive(int itemPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "ItemPk" });
            whereList.Add(new ColumnWithValue { ColumnName = "Active", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "ItemPk", Comparison = QComparison.EQUALS, ColumnValue = itemPk});
            var itemQuery = new Query { TableName = "fItems", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(itemQuery).Rows.Count > 0)
            {
                return true;
            }

            return false;
        }


        public int GetCurrencyKey(string currencyCode)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "CurPk" });
            whereList.Add(new ColumnWithValue { ColumnName = "CurCode", Comparison = QComparison.EQUALS, ColumnValue = currencyCode });
            var currencyQuery = new Query { TableName = "fCurrency", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            var dtCurrency = new DataTable();
            dtCurrency = readMasterData.ExcecuteSelect(currencyQuery);
            if (dtCurrency.Rows.Count > 0)
            {
                return (int)dtCurrency.Rows[0]["CurPk"];
            }

            return 0;
        }

        public DataTable GetTaxCombinationDetails(int taxCombPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxComPk" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxPk" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxRate" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxSeq" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxName" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxPer" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fTaxDet", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });


            whereList.Add(new ColumnWithValue { ColumnName = "fTaxDet.TaxComPk", Comparison = QComparison.EQUALS, ColumnValue = taxCombPk });
            var taxQuery = new Query { TableName = "fTaxDet", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            var dtTax = new DataTable();
            dtTax = readMasterData.ExcecuteSelect(taxQuery);
            return dtTax;

        }

        public DataTable GetCostDetails(string costCode = "")
        {
            var readMasterData = DatabaseAccess.Instance;

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "CostPk" });
            columnList.Add(new ColumnWithValue { ColumnName = "CostCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "CostName" });
            columnList.Add(new ColumnWithValue { ColumnName = "CostCon" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level1" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level2" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level3" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level4" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level5" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level6" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level7" });
            columnList.Add(new ColumnWithValue { ColumnName = "Level8" });

            if (costCode != string.Empty)
            {
                whereList.Add(new ColumnWithValue { ColumnName = "CostCode", Comparison = QComparison.EQUALS, ColumnValue = costCode });
            }

            var dtCostDet = new DataTable();
            Query costQuery;
            if (costCode == string.Empty)
            {
                dtCostDet = GetAllCostCodeDetails();
                //costQuery = new Query { TableName = "fCost", QType = QueryType.SELECT, ColValues = columnList.ToArray() };
            }
            else
            {
                costQuery = new Query { TableName = "fCost", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
                dtCostDet = readMasterData.ExcecuteSelect(costQuery);
            }

            return dtCostDet;
        }

        public DataTable GetCostCentreAccountDetails(Modes FormMode, int costPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            var dtCostAccounts = new DataTable();

            if (FormMode == Modes.New)
            {

                string sql = "select a.*,1 as Upd, 0 as CostPk,a.AccDesc as AccDescCopy  from fConAcc a inner join fGlfSc b ON a.AccSortPk = b.AccSortPk WHERE  b.AccTyp =@accTyp";

                var parametersCost = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@accTyp",ParamValue="P"},
                                 };

                var costCentreAccountQuery = new Query { QueryString = sql, QType = QueryType.SELECTOTHER, ParameterList = parametersCost.ToArray() };
                dtCostAccounts = readMasterData.ExcecuteSelect(costCentreAccountQuery, true);
            }

            else
            {
                string sqlQuery = "Select a.*,b.AccDesc as AccDescCopy,1 as Upd,b.* From fGlfAcc a,fConAcc b Where a.CostPk=@costPk and LEFT(a.AccCode,8)=LEFT(b.AccCode,8) Order by a.AccCode";

                var parametersCost = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@costPk",ParamValue=costPk},
                                 };

                var costCentreAccountQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = parametersCost.ToArray() };
                dtCostAccounts = readMasterData.ExcecuteSelect(costCentreAccountQuery, true);

                // sqlQuery = "Select DISTINCT a.*,a.AccDesc as AccDescCopy,0 as Upd,b.* From fConAcc a,fGlfAcc b Where LEFT(RTRIM(LTRIM(a.AccCode)),8)= LEFT(RTRIM(LTRIM(b.AccCode)),8)  AND LEFT(RTRIM(LTRIM(a.AccCode)),8) NOT IN (select LEFT(RTRIM(LTRIM(c.AccCode)),8) from fGlfAcc c WHERE c.CostPk=@costPk)";
                sqlQuery = "Select DISTINCT a.AccCode From fConAcc a,fGlfAcc b Where LEFT(RTRIM(LTRIM(a.AccCode)),8)= LEFT(RTRIM(LTRIM(b.AccCode)),8)  AND LEFT(RTRIM(LTRIM(a.AccCode)),8) NOT IN (select LEFT(RTRIM(LTRIM(c.AccCode)),8) from fGlfAcc c WHERE c.CostPk=@costPk)";
                DataTable dtNewCostAccounts;
                DataTable dtAccountDetails;
                var parametersCostAccounts = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@costPk",ParamValue=costPk},
                                 };


                costCentreAccountQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = parametersCostAccounts.ToArray() };
                dtNewCostAccounts = readMasterData.ExcecuteSelect(costCentreAccountQuery, true);

                sqlQuery = "Select a.*,a.AccDesc as AccDescCopy,0 as Upd From fConAcc a ";
                costCentreAccountQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER };
                dtAccountDetails = readMasterData.ExcecuteSelect(costCentreAccountQuery, true);

                DataColumn[] primaryKeys = new DataColumn[1];
                primaryKeys[0] = dtAccountDetails.Columns["AccCode"];
                dtAccountDetails.PrimaryKey = primaryKeys;

                DataTable dtNewCostAccountDetails = dtAccountDetails.Clone();

                foreach (DataRow dr in dtNewCostAccounts.Rows)
                {
                    DataRow newRow = dtNewCostAccountDetails.NewRow();
                    DataRow findRow = dtAccountDetails.Rows.Find(dr["AccCode"].ToString());

                    if (findRow != null)
                    {
                        foreach (DataColumn dtc in dtAccountDetails.Columns)
                        {
                            newRow[dtc.ColumnName] = findRow[dtc.ColumnName];
                        }

                        dtNewCostAccountDetails.Rows.Add(newRow);
                    }
                }


                foreach (DataRow drAccounts in dtNewCostAccountDetails.Rows)
                //  foreach (DataRow drAccounts in dtNewCostAccounts.Rows)
                {
                    DataRow dtNewRow = dtCostAccounts.NewRow();

                    foreach (DataColumn dtc in dtNewCostAccountDetails.Columns)
                    {
                        dtNewRow[dtc.ColumnName] = drAccounts[dtc.ColumnName];
                    }

                    dtNewRow["CostPk"] = costPk;

                    dtCostAccounts.Rows.Add(dtNewRow);
                }


            }

            return dtCostAccounts;
        }

        public bool IsCostCodeDuplicate(string costCode)
        {
            var dtCost = GetCostDetails(costCode);
            if (dtCost.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }


        public bool IsCostCodeUsedInOtherLevels(int costPk)
        {

            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            var dtCost = new DataTable();
            columnList.Add(new ColumnWithValue { ColumnName = "CostCode" });
            whereList.Add(new ColumnWithValue { ColumnName = "CostPk", Comparison = QComparison.NOTEQUAL, ColumnValue = costPk, Level = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level2", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.AND, Level = 2 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level3", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.OR, Level = 2 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level4", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.OR, Level = 2 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level5", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.OR, Level = 2 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level6", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.OR, Level = 2 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level7", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.OR, Level = 2 });
            whereList.Add(new ColumnWithValue { ColumnName = "Level8", Comparison = QComparison.EQUALS, ColumnValue = costPk, ColumnConnector = QLogicOperator.OR, Level = 2 });

            var costQuery = new Query { TableName = "fCost", QType = QueryType.SELECT, WhereClause = whereList.ToArray(), ColValues = columnList.ToArray() };
            dtCost = readMasterData.ExcecuteSelect(costQuery);

            if (dtCost.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }


        }

        public DataTable GetAllCostCodeDetails()
        {
            var readMasterData = DatabaseAccess.Instance;

            string sql = "SELECT A.CostPk,A.CostCode,A.CostName ,(select  CostCode from dbo.fCost where CostPk = A.level1) as level1 " +
                      " ,(select  CostCode from dbo.fCost where CostPk = A.level2) as level2 " +
                      " ,(select  CostCode from dbo.fCost where CostPk = A.level3) as level3 " +
                   ",(select  CostCode from dbo.fCost where CostPk = A.level4) as level4 " +
                  " ,(select  CostCode from dbo.fCost where CostPk = A.level5) as level5 " +
                  " ,(select  CostCode from dbo.fCost where CostPk = A.level6) as level6 " +
                  ",(select  CostCode from dbo.fCost where CostPk = A.level7) as level7 " +
                  "  ,(select  CostCode from dbo.fCost where CostPk = A.level8) as level8 " +
                    " FROM dbo.fCost as A";

            var costQuery = new Query { QueryString = sql, QType = QueryType.SELECTOTHER };

            var dtCostDet = readMasterData.ExcecuteSelect(costQuery);
            return dtCostDet;
        }

        public DataTable GetCurrencyRate(int currencyPk,DateTime transactionDate)
        {
            var db = DatabaseAccess.Instance;
            List<ParameterWithValue> paramList = new List<ParameterWithValue>();
            paramList.Add(new ParameterWithValue("@CurrencyPk", currencyPk));
            paramList.Add(new ParameterWithValue("@TransactionDate", transactionDate));

            SpCaller spCaller = new SpCaller() { SpName = "SP_GET_CURRENCY_RATE", SpParamValues = paramList.ToArray() };
            return db.ExcecuteSelect(spCaller);
        }

        public bool UserWiseLocationValidity(object locCode, string userName)
        {
            var db = DatabaseAccess.Instance;
            List<ParameterWithValue> paramList = new List<ParameterWithValue>();
            paramList.Add(new ParameterWithValue("@LocCode", locCode));
            paramList.Add(new ParameterWithValue("@UserName", userName));
            paramList.Add(new ParameterWithValue("@Result", false, SqlDbType.Bit, ParameterDirection.InputOutput));

            SpCaller spCaller = new SpCaller() { SpName = "SP_USER_WISE_LOCATION_VALIDITY", SpParamValues = paramList.ToArray() };
            return (bool)db.Excecute(spCaller, "@Result");
        }

       

        public string GetTaxCombinationCode(int taxCombPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "TaxComCode" });         

            whereList.Add(new ColumnWithValue { ColumnName = "TaxComPk", Comparison = QComparison.EQUALS, ColumnValue = taxCombPk });
            var taxQuery = new Query { TableName = "fTaxHed", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            var dtTax = new DataTable();
            dtTax = readMasterData.ExcecuteSelect(taxQuery);
            return dtTax.Rows[0]["TaxComCode"].ToString();

        }

        public int GetConCodeStatusDetails(string conCode)
        {
            var readMasterData = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
                     
            columnList.Add(new ColumnWithValue { ColumnName = "Status" });

            whereList.Add(new ColumnWithValue { ColumnName = "ConCode", Comparison = QComparison.EQUALS, ColumnValue = conCode });
            var conCodeQuery = new Query { TableName = "fStConfig", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };

            var dtConStatus = (DataTable)(readMasterData.ExcecuteSelect(conCodeQuery));           

            
            return Convert.ToInt32(dtConStatus.Rows[0]["Status"]);
        }

        public bool IsDebtorActive(int debtorPk)
        {
            var readMasterData = DatabaseAccess.Instance;
            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue { ColumnName = "DebCode" });
            whereList.Add(new ColumnWithValue { ColumnName = "Status", Comparison = QComparison.EQUALS, ColumnValue = 1 });
            whereList.Add(new ColumnWithValue { ColumnName = "DebPk", Comparison = QComparison.EQUALS, ColumnValue = debtorPk });
            var supplierQuery = new Query { TableName = "fDebtor", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray() };
            if (readMasterData.ExcecuteSelect(supplierQuery).Rows.Count > 0)
            {
                return true;
            }

            return false;
        }
       
    }
}
